﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Sistema_de_Patrimonio.SDP_DAL
{
    class Existencia_DAL
    {

        public DataSet ExistenciaListCmboxCuenta()
        {
            //EL @ es para dividir una línea de código en 2
            SqlConnection Connection = Common_Files.Connection.OpenConecctionDeposito();
            SqlDataAdapter Command = new SqlDataAdapter(@"Select distinct art_cuenta as 'Cuenta' from articulos order by 1 asc", Connection);
            DataSet DataSet = new DataSet();
            Command.Fill(DataSet);
            return (DataSet);
        }

        public DataSet ExistenciaListCmboxFecha()
        {
            //EL @ es para dividir una línea de código en 2
            SqlConnection Connection = Common_Files.Connection.OpenConecction();
            SqlDataAdapter Command = new SqlDataAdapter(@"Select distinct mov_fecha as 'Fecha' from movimientos order by 1 desc", Connection);
            DataSet DataSet = new DataSet();
            Command.Fill(DataSet);
            return (DataSet);
        }

        public DataSet ExistenciaListCmboxArticulo(UInt32 cuenta)
        {
            //EL @ es para dividir una línea de código en 2
            SqlConnection Connection = Common_Files.Connection.OpenConecctionDeposito();
            SqlDataAdapter Command = new SqlDataAdapter(@"Select distinct art_articulo as 'Articulo' from articulos where art_cuenta = '" + cuenta + "' order by 1 asc", Connection);
            DataSet DataSet = new DataSet();
            Command.Fill(DataSet);
            return (DataSet);
        }

        public DataSet CargarArticulo(UInt32 Cuenta, UInt32 Articulo)
        {
            SqlConnection Connection = Common_Files.Connection.OpenConecctionDeposito();
            SqlDataAdapter Command = new SqlDataAdapter(@"SELECT art_descripcion as Descripción, art_stock as Saldo, art_medida as Medida, art_unidad as Unidad, art_gramaje as Gramaje FROM articulos where art_cuenta = '" + Cuenta + "' and art_articulo = '" + Articulo + "' order by 1 desc ", Connection);
            DataSet DataSet = new DataSet();
            Command.Fill(DataSet);
            return (DataSet);
        }

        public DataSet CargarMovimientosArticulo(UInt32 Cuenta, UInt32 Articulo)
        {
            SqlConnection Connection = Common_Files.Connection.OpenConecction(); //Conexion patrimonio
            SqlDataAdapter Command = new SqlDataAdapter(@"SELECT mov_codigo as Codigo, mov_fecha as Fecha, mov_comprobante as Comprobante, mov_procedencia as Procedencia, mov_tipo as Tipo, mov_salidaentrada as Movimiento, mov_saldo as Saldo FROM movimientos where mov_cuenta = '" + Cuenta + "' and mov_articulo = '" + Articulo + "' order by 1 desc ", Connection);
            DataSet DataSet = new DataSet();
            Command.Fill(DataSet);
            return (DataSet);
        }

        public Decimal CargarSaldoArticulo(UInt32 Cuenta, UInt32 Articulo)
        {
            SqlConnection Connection = Common_Files.Connection.OpenConecction(); //Conexion patrimonio
            SqlDataAdapter Command = new SqlDataAdapter(@"SELECT Top 1 mov_codigo as Codigo, mov_fecha as Fecha, mov_comprobante as Comprobante, mov_procedencia as Procedencia, mov_tipo as Tipo, mov_salidaentrada as Movimiento, mov_saldo as Saldo FROM movimientos where mov_cuenta = '" + Cuenta + "' and mov_articulo = '" + Articulo + "' order by 1 desc ", Connection);
            DataSet DataSet = new DataSet();
            Command.Fill(DataSet);
            if (DataSet.Tables[0].Rows.Count > 0)
                return (Convert.ToInt32(DataSet.Tables[0].Rows[0].ItemArray[6]));
            else return 0;
        }

        public void InsertarMovimiento(UInt32 Cuenta, UInt32 Articulo, DateTime Fecha, String Comprobante, String Procedencia, String Tipo, Decimal Movimiento, Decimal Saldo)
        {
            //EL @ es para dividir una línea de código en 2
            SqlConnection Connection = Common_Files.Connection.OpenConecction();
            SqlCommand Command = new SqlCommand(@"insert into Movimientos (mov_cuenta, mov_articulo, mov_fecha, mov_comprobante, mov_procedencia, mov_tipo, mov_salidaentrada, mov_saldo) 
                                                                values ('" + Cuenta + "', '" + Articulo + "', '" + Fecha.ToShortDateString() + "', '" + Comprobante + "', '" + Procedencia + "', '" + Tipo + "', '" + Movimiento + "', '" + Saldo + "')", Connection);
            Common_Files.Connection.CloseConecction(Connection, Command);

            //Actualizo saldo Deposito
            SqlConnection Connection2 = Common_Files.Connection.OpenConecctionDeposito();
            SqlCommand Command2 = new SqlCommand(@"update Articulos set art_saldoPatrimonio = '" + Saldo + "' where art_cuenta = '" + Cuenta + "' and art_articulo = '" + Articulo + "' ", Connection2);
            Common_Files.Connection.CloseConecction(Connection2, Command2);
        }

        public void ModificarMovimiento(UInt32 Codigo, UInt32 Cuenta, UInt32 Articulo, DateTime Fecha, String Comprobante, String Procedencia, String Tipo, Decimal Movimiento, Decimal Saldo)
        {
            //EL @ es para dividir una línea de código en 2
            SqlConnection Connection = Common_Files.Connection.OpenConecction();
            SqlCommand Command = new SqlCommand(@"update Movimientos set mov_fecha = '" + Fecha.ToShortDateString() + "', mov_comprobante = '" + Comprobante + "', mov_procedencia = '" + Procedencia + "', mov_tipo = '" + Tipo + "',  mov_salidaentrada = '" + Movimiento + "', mov_saldo = '" + Saldo + "' where mov_codigo = '" + Codigo + "' ", Connection);
            Common_Files.Connection.CloseConecction(Connection, Command);

            //Actualizo saldo Deposito
            SqlConnection Connection2 = Common_Files.Connection.OpenConecctionDeposito();
            SqlCommand Command2 = new SqlCommand(@"update Articulos set art_saldoPatrimonio = '" + Saldo + "' where art_cuenta = '" + Cuenta + "' and art_articulo = '" + Articulo + "' ", Connection2);
            Common_Files.Connection.CloseConecction(Connection2, Command2);
        }


        public void ActualizarSaldo(UInt32 Cuenta, UInt32 Articulo, Decimal Saldo)
        {
            //EL @ es para dividir una línea de código en 2
            SqlConnection Connection = Common_Files.Connection.OpenConecctionDeposito();
            SqlCommand Command = new SqlCommand(@"update Articulos set art_saldoPatrimonio = '" + Saldo + "' where art_cuenta = '" + Cuenta + "' and art_articulo = '" + Articulo + "' ", Connection);
            Common_Files.Connection.CloseConecction(Connection, Command);
        }

        public void MovimientoDelete(UInt32 Codigo)
        {
            SqlConnection Connection = Common_Files.Connection.OpenConecction();
            SqlCommand Command = new SqlCommand("delete from Movimientos where mov_codigo = '" + Codigo + "'", Connection);
            Common_Files.Connection.CloseConecction(Connection, Command);
        }

    }
}
